package com.apache.excp.core.impl.exp.utils;

import java.io.BufferedInputStream;
import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.FileOutputStream;
import java.io.IOException;
import java.io.OutputStream;
import java.lang.reflect.Field;
import java.lang.reflect.InvocationTargetException;
import java.lang.reflect.Method;
import java.text.SimpleDateFormat;
import java.util.*;
import java.util.regex.Matcher;
import java.util.regex.Pattern;

import javax.swing.JOptionPane;

import com.apache.api.vo.ParamsVo;
import com.apache.excp.core.model.ExportVo;
import com.apache.tools.DataMap;
import net.sf.json.JSONArray;
import net.sf.json.JSONObject;
import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFCellStyle;
import org.apache.poi.hssf.usermodel.HSSFClientAnchor;
import org.apache.poi.hssf.usermodel.HSSFComment;
import org.apache.poi.hssf.usermodel.HSSFFont;
import org.apache.poi.hssf.usermodel.HSSFPatriarch;
import org.apache.poi.hssf.usermodel.HSSFRichTextString;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.hssf.util.HSSFColor;
import org.apache.poi.ss.usermodel.BorderStyle;
import org.apache.poi.ss.usermodel.FillPatternType;
import org.apache.poi.ss.usermodel.HorizontalAlignment;
import org.apache.poi.ss.usermodel.IndexedColors;
import org.apache.poi.ss.util.CellRangeAddress;

/**
 * 利用开源组件POI3.0.2动态定义导出EXCEL文档模板
 *
 * @author fpc
 * @version v1.0
 *
 */
public class ExcelExpTemplateUtil
{
    /**
     * @param objName
     *            表格标题名
     * @param dataList
     *            需要显示的数据集合,集合中一定要放置符合javabean风格的类的对象。此方法支持的
     *            javabean属性的数据类型有基本数据类型及String,Date,byte[](图片数据)
     * @param out
     *            与输出设备关联的流对象，可以将EXCEL文档导出到本地文件或者网络中
     */
    @SuppressWarnings("unchecked")
    public void exportExcel(String objName,
                            List<JSONObject> dataList, OutputStream out)
    {

        int size = dataList.size();
        // 声明一个工作薄
        HSSFWorkbook workbook = new HSSFWorkbook();
        // 生成一个表格
        HSSFSheet sheet = workbook.createSheet(objName);
        // 设置表格默认列宽度为15个字节
        sheet.setDefaultColumnWidth((short) 30);
        //第一行合并单元格
        CellRangeAddress callRangeAddress = new CellRangeAddress(1,1,0,size);
//第三行合并单元格
        CellRangeAddress callRangeAddress3 = new CellRangeAddress(3,3,0,size);
        // 产生表格标题行
        sheet.addMergedRegion(callRangeAddress);
        sheet.addMergedRegion(callRangeAddress3);
        /*
        HSSFCellStyle borderStyle = workbook.createCellStyle();
        borderStyle.setBorderBottom(BorderStyle.THIN);//下边框
        borderStyle.setBorderLeft(BorderStyle.THIN);//左边框
        borderStyle.setBorderRight(BorderStyle.THIN);//右边框
        borderStyle.setBorderTop(BorderStyle.THIN); //上边框
        */

        HSSFCellStyle style = workbook.createCellStyle();//创建样式

        style.setAlignment(HorizontalAlignment.CENTER);//水平居中
        style.setFillPattern(FillPatternType.SOLID_FOREGROUND);//设置背景颜色
        style.setFillForegroundColor(IndexedColors.SKY_BLUE.index); //浅蓝色
        HSSFFont font = workbook.createFont();
        font.setBold(true);//设置是否加粗
        font.setColor(IndexedColors.WHITE.index);//设置白色字体
        style.setFont(font);//添加字体到样式

        HSSFRow row = sheet.createRow(0);
        HSSFCell cell = row.createCell(0);
        cell.setCellStyle(style);
        cell.setCellValue("序号");
        HSSFRow row1 = sheet.createRow(1);
        HSSFCell cell1 = row1.createCell(0);
        cell1.setCellValue("<#list "+objName+" as obj>");
        HSSFRow row2 = sheet.createRow(2);
        HSSFCell cell2 = row2.createCell(0);
        cell2.setCellValue("${obj_index+1}");



        for (int i =0;i<size;i++){

            JSONObject columnMap = (JSONObject) dataList.get(i);
            String columnStr = (String) columnMap.get("key");
            String titleStr = (String) columnMap.get("value");
            HSSFCell cell1temp = row2.createCell(i+1);
            HSSFCell cell2temp = row.createCell(i+1);
            cell2temp.setCellStyle(style);
            cell1temp.setCellValue("${obj."+objName+"."+columnStr+"!''}");
            cell2temp.setCellValue(titleStr);
        }

        HSSFRow row3 = sheet.createRow(3);
        HSSFCell cell3 = row3.createCell(0);
        cell3.setCellValue("</#list>");

        try
        {
            workbook.write(out);
        }
        catch (IOException e)
        {
            e.printStackTrace();
        }
    }

    public static void main(String[] args)
    {
        // 测试
        ExcelExpTemplateUtil ex = new ExcelExpTemplateUtil();
        try
        {
            ExcelExpTemplateUtil expTemplateUtil = new ExcelExpTemplateUtil();
            String str = "[{\"objName\":\"元器件表\",\"objColumns\":[{\"key\":\"testInformation\",\"value\":\"试验信息\"},{\"key\":\"compContent\",\"value\":\"元器件内容\"},{\"key\":\"createTime\",\"value\":\"创建时间\"},{\"key\":\"radiationResistanceIndex\",\"value\":\"抗辐照指标\"}]}]";
            JSONArray jsonArrays = JSONArray.fromObject(str);
            Map<String,Object> map = new HashMap<String,Object>();
            for (int i = 0; i<jsonArrays.size();i++)
            {
                JSONObject jsonObject = (JSONObject)jsonArrays.get(i);
                JSONArray jsonColumnsArray = (JSONArray)jsonObject.get("objColumns");
                String objNamestr = (String)jsonObject.get("objName");
                map.put(objNamestr,jsonColumnsArray);
            }
            for (String key : map.keySet()) {
                OutputStream out = new FileOutputStream("E://aa.xls");
                expTemplateUtil.exportExcel(key,(List<JSONObject>) map.get(key),out);
                out.close();
            }


            System.out.println("excel导出成功！");
        }
        catch (FileNotFoundException e)
        {
            e.printStackTrace();
        }
        catch (IOException e)
        {
            e.printStackTrace();
        }
    }
}

